# %%
import os
import shutil

from openpyxl import load_workbook

split_file = 'aa.xlsx'

target_dir = 'dist'
if os.path.exists(target_dir):
    shutil.rmtree(target_dir)
os.mkdir(target_dir)

from_wb = load_workbook(split_file)

nums = range(1, 9)

all_row_start = 2
all_row_end = 1000

CLASS_COL = 0
SEX_COL = 3


def del_ws_rows(ws, to_del_rows):
    to_del_rows.reverse()
    for r in to_del_rows:
        ws.delete_rows(r, 1)


for c in nums:
    print(c, 'ban')
    one_file = f'{target_dir}/{c}班-全部.xlsx'
    from_wb.save(one_file)

    new_wb = load_workbook(one_file)
    new_ws = new_wb.active
    not_current_classes = []
    i = 1
    for row in new_ws.iter_rows(min_row=2):
        i += 1
        print('rr', row[CLASS_COL].value != str(c), type(row[CLASS_COL].value), row[0].value, row[1].value, c)
        if row[CLASS_COL].value != c:
            not_current_classes.append(i)

    print('nc', not_current_classes)
    del_ws_rows(new_ws, not_current_classes)

    # before_range = (all_row_start, to_keep[0]-all_row_start)
    # after_range = (to_keep[-1]+1, all_row_end-to_keep[-1])

    # print(before_range, after_range)

    # if after_range[1] > 0:
    #     new_ws.delete_rows(after_range[0], after_range[1])
    # if before_range[1] > 0:
    #     new_ws.delete_rows(before_range[0], before_range[1])

    # new_ws.delete_cols(CLASS_COL+1)

    # new_ws.cell(1,1).value = f'{c}班'
    new_wb.save(one_file)
    print('分班完成')
    
    continue

    # 整男生
    male_wb = load_workbook(one_file)
    male_ws = male_wb.active

    male_list = []
    female_list = []
    j = 2
    for row in male_ws.iter_rows(min_row=3):
        j += 1
        if row[SEX_COL].value == '男':
            male_list.append(j)
        else:
            female_list.append(j)

    print(f'{c}班男生人数{len(male_list)}女生人数{len(female_list)}')

    male_file = f'{target_dir}/{c}班-男生.xlsx'
    female_file = f'{target_dir}/{c}班-女生.xlsx'

    del_ws_rows(male_ws, female_list)

    # female_list.reverse()
    # for m in female_list:
    #     # print('da', m)
    #     male_ws.delete_rows(m, 1)
    male_ws.delete_cols(11)
    male_ws.delete_cols(9)
    ind = 1
    for r in male_ws.iter_rows(min_row=3):
        r[0].value = ind
        r[1].value = f'{c}班'
        ind += 1

    male_wb.save(male_file)

    female_wb = load_workbook(one_file)
    female_ws = female_wb.active

    del_ws_rows(female_ws, male_list)

    # male_list.reverse()
    # for f in male_list:
    #     # print('da', m)
    #     female_ws.delete_rows(f, 1)

    female_ws.delete_cols(10)
    female_ws.delete_cols(8)

    ind = 1
    for r in female_ws.iter_rows(min_row=3):
        r[0].value = ind
        r[1].value = f'{c}班'
        ind += 1
    female_wb.save(female_file)

    print(male_list, female_list)



